import pymysql
import threading

class DB:
    """
    数据库基类，提供最基本的数据库操作
    """
    def __init__(self):
        # """
        # 初始化数据库连接
        # """
        # self.db = pymysql.connect(
        #     user='root',
        #     password='123456',
        #     host='localhost',
        #     db='软工管')
        # self.cursor = self.db.cursor(pymysql.cursors.DictCursor)
        self.user = 'root'
        self.pwd = '010507'
        self.host = 'localhost'
        self.db = '软工管'
    def execute(self, command) -> list:
        """
        执行sql语句
        :param command: sql语句
        :return: 执行结果
        """
        counter_lock = threading.Lock()
        counter_lock.acquire()
        conn = pymysql.connect(user=self.user, password=self.pwd, host=self.host, db=self.db)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        try:
            cursor.execute(command)
            conn.commit()
        except Exception as e:
            counter_lock.release()
            cursor.close()
            conn.close()
            return e
        else:
            data = cursor.fetchall()
            counter_lock.release()
            cursor.close()
            conn.close()
            return data

class Database(DB):
    def __init__(self):
        super().__init__()
    
    # ============== query ===============
    def query_user(self, username) -> list:
        """
        查询特定 username 的用户
        :param username: 用户名
        :return: 查询结果
        """
        command = "select * from user where username = '%s'" % username
        return self.execute(command)
    def query_article(self, id) -> list:
        """
        查询特定 id 的文章
        :param id: 文章id
        :return: 查询结果
        """
        command = "select * from article where id = '%s'" % id
        return self.execute(command)
    def query_article_id(self, path) -> int:
        """
        查询文章id
        :param path: 文章路径
        :return:
        """
        command = "select id from article where path = '%s'" % path
        return self.execute(command)
    def query_comment(self, id) -> list:
        """
        查询评论
        :param id: 评论 id
        :return:
        """
        command = "select * from comment where id = '%s'" % id
        return self.execute(command)
    def query_all_articles(self, course) -> list:
        """
        查询特定课程的所有文章
        :param course: 课程名
        :return: 查询结果
        """
        command = "select * from article where course = '%s'" % course
        return self.execute(command)
    def query_recent_articles(self, username) -> list:
        """
        查询特定课程的所有文章
        :param course: 课程名
        :return: 查询结果
        """
        command = "select course, title from article where uploader = '%s' order by id desc limit 2" % username
        res = self.execute(command)
        return res
    def query_course_comments(self, course) -> list:
        """
        查询特定课程的所有评论
        :param course: 课程名
        :return: 查询结果
        """
        
        command = "select * from comment where course = '%s'" % course
        return self.execute(command)
    def query_user_comments(self, commenter) -> list:
        """
        查询特定用户的所有评论
        :param commenter: 用户名
        :return: 查询结果
        """
        command = "select * from comment where commenter = '%s'" % commenter
        return self.execute(command)
    def query_user_comment_number(self, commenter) -> str:
        """
        查询特定用户的所有评论
        :param commenter: 用户名
        :return: 查询结果
        """

        command = "select count(id) from comment where commenter = '%s'" % commenter

        res = self.execute(command)

        return res
    def query_user_comment_likes_number(self, commenter) -> str:
        """
        查询特定用户的所有评论
        :param commenter: 用户名
        :return: 查询结果
        """

        command = "select sum(likes) from comment where commenter = '%s'" % commenter

        res = self.execute(command)

        return res
    def query_article_scores(self, id) -> list:
        """
        查询特定文章的评分
        :param id: 文章id
        :return: 查询结果
        """
        command = "select score from score where id = '%s'" % id
        return self.execute(command)
    def query_user_scores(self, scorer) -> list:
        """
        查询特定用户的评分
        :param scorer: 用户名
        :return: 查询结果
        """
        command = "select * from score where scorer = '%s'" % scorer
        return self.execute(command)
    def query_user_article_score(self, id, username) -> list:
        """
        查询特定用户对特定文章的评分
        :param id: 文章id
        :param username: 用户名
        :return: 查询结果
        """
        command = "select score from score where id = '%s' and scorer = '%s'" % (id, username)
        return self.execute(command)
    def query_user_votes(self, id, voter) -> list:
        """
        查询特定用户是否对特定评论投过票
        :param id: 评论 id
        :param voter: 用户名
        :return: 查询结果
        """
        command = "select `condition` from vote where id = '%s' and voter = '%s'" % (id, voter)
        return self.execute(command)
    def query_course(self, name) -> list:
        """
        查询课程
        :param name: 课程名
        :return:
        """
        command = "select path from course where name = '%s'" % name
        return self.execute(command)

    # ============== add ===============
    def add_user(self, username, password, isAdmin, secure_question, secure_answer) -> None:
        """
        添加用户
        :param username: 用户名
        :param password: 密码
        :param isAdmin: 是否为管理员
        :param secure_question: 安全问题
        :param secure_answer: 安全问题答案
        :return:
        """
        command = "insert into user values ('%s', '%s', %s, '%s', '%s')" % (username, password, isAdmin, secure_question, secure_answer)
        self.execute(command)
        # self.db.commit()
    def add_article(self, path, course, uploader, title) -> None:
        """
        添加文章
        :param path: 文章路径
        :param course: 课程名
        :param uploader: 上传者
        :return:
        """
        command = "insert into article (path, course, uploader, title) values ('%s', '%s', '%s', '%s')" % (path, course, uploader, title)
        self.execute(command)
        # self.db.commit()
    def add_comment(self, course, commenter, comment) -> None:
        """
        添加评论
        :param course: 课程名
        :param commenter: 评论者
        :param comment: 评论内容
        :param time: 评论时间
        :return:
        """
        command = "insert into comment (course, commenter, comment) values ('%s', '%s', '%s')" % (course, commenter, comment)
        self.execute(command)
        # self.db.commit()
    def add_score(self, id, scorer, score) -> None:
        """
        添加评分
        :param id: 文章id
        :param scorer: 评分者
        :param score: 评分
        :return:
        """
        command = "insert into score values ('%s', '%s', %s)" % (id, scorer, score)
        self.execute(command)
        # self.db.commit()
    def add_vote(self, id, voter, condition) -> None:
        """
        添加投票
        :param id: 评论id
        :param voter: 投票者
        :param condition: 投票结果
        :return:
        """
        command = "insert into vote values ('%s', '%s', %s)" % (id, voter, condition)
        self.execute(command)
        # self.db.commit()
    def add_course(self, name, path) -> None:
        """
        添加课程
        :param name: 课程名
        :param path: 课程路径
        :return:
        """
        command = "insert into course values ('%s', '%s')" % (name, path)
        self.execute(command)
        # self.db.commit()
    
    # ============== update ===============
    def update_user(self, username, password) -> None:
        """
        更新用户信息
        :param username: 用户名
        :param password: 密码
        :return:
        """
        command = "update user set password = '%s' where username = '%s'" % (password, username)
        self.execute(command)
        # self.db.commit()
    def become_admin(self, username) -> None:
        """
        用户升级为管理员
        :param username: 用户名
        :return:
        """
        command = "update user set isAdmin = 1 where username = '%s'" % username
        self.execute(command)
        # self.db.commit()
    def update_article(self, id, path, course, uploader, title) -> None:
        """
        更新文章信息
        :param id: 文章id
        :param path: 文章路径
        :param course: 课程名
        :param uploader: 上传者
        :return:
        """
        command = "update article set course = '%s', uploader = '%s', title = '%s' where id = '%s'" % (course, uploader, title, id)
        self.execute(command)
        # self.db.commit()
    def update_comment(self, id, comment):
        """
        更新评论
        :param id: 评论id
        :param comment: 评论内容
        :return:
        """
        command = "update comment set comment = '%s', time = current_timestamp where id = '%s'" % (comment, id)
        self.execute(command)
        # self.db.commit()
    def update_score(self, id, scorer, score) -> None:
        """
        更新评分
        :param id: 文章id
        :param scorer: 评分者
        :param score: 评分
        :return:
        """
        command = "update score set score = %s where id = '%s' and scorer = '%s'" % (score, id, scorer)
        self.execute(command)
        # self.db.commit()
    def like_comment(self, id, voter) -> None:
        """
        点赞评论
        :param id: 评论id
        :param voter: 投票者
        :return:
        """
        command = "update comment set likes = likes + 1 where id = '%s'" % id
        self.execute(command)

        # self.db.commit()
        self.add_vote(id, voter, True)
    def dislike_comment(self, id, voter) -> None:
        """
        点踩评论
        :param id: 评论id
        :param voter: 投票者
        :return:
        """
        command = "update comment set dislikes = dislikes + 1 where id = '%s'" % id
        self.execute(command)
        # self.db.commit()
        self.add_vote(id, voter, False)

    # ============== delete ===============
    def delete_user(self, username) -> None:
        """
        删除用户
        :param username: 用户名
        :return:
        """
        command = "delete from user where username = '%s'" % username
        self.execute(command)
        # self.db.commit()
    def delete_article(self, id) -> None:
        """
        删除文章
        :param id: 文章id
        :return:
        """
        command = "delete from article where id = '%s'" % id
        self.execute(command)
        # self.db.commit()
    def delete_comment(self, id) -> None:
        """
        删除评论
        :param id: 评论id
        :return:
        """
        command = "delete from comment where id = '%s'" % id
        self.execute(command)
        # self.db.commit()
    def delete_score(self, id, scorer) -> None:
        """
        删除评分
        :param id: 文章id
        :param scorer: 评分者
        :return:
        """
        command = "delete from score where id = '%s' and scorer = '%s'" % (id, scorer)
        self.execute(command)
        # self.db.commit()
    def delete_vote(self, id, voter) -> None:
        """
        删除投票
        :param id: 评论id
        :param voter: 投票者
        :return:
        """
        command = "delete from vote where id = '%s' and voter = '%s'" % (id, voter)
        self.execute(command)
        # self.db.commit()
    def delete_like(self, id, voter) -> None:
        """
        取消点赞
        :param id: 评论id
        :param voter: 投票者
        :return:
        """
        command = "update comment set likes = likes - 1 where id = '%s'" % id
        self.execute(command)
        # self.db.commit()
        self.delete_vote(id, voter)
    def delete_dislike(self, id, voter) -> None:
        """
        取消点踩
        :param id: 评论id
        :param voter: 投票者
        :return:
        """
        command = "update comment set dislikes = dislikes - 1 where id = '%s'" % id
        self.execute(command)
        # self.db.commit()
        self.delete_vote(id, voter)
    def delete_course(self, name) -> None:
        """
        删除课程
        :param name: 课程名
        :return:
        """
        command = "delete from course where name = '%s'" % name
        self.execute(command)
        # self.db.commit()

    # ============== review operations ===============
    def add_review(self, id) -> None:
        """
        添加审核
        :param id: 文章id
        :return:
        """
        command = "insert into review (id, reviewer, `condition`) values ('%s', null, 'unreview')" % id
        self.execute(command)
        # self.db.commit()
    def pass_review(self, id, reviewer) -> None:
        """
        通过审核
        :param id: 文章id
        :param reviewer: 审核者
        :return:
        """
        command = "update review set `condition` = 'pass', reviewer = '%s' where id = '%s'" % (reviewer, id)
        self.execute(command)
        # self.db.commit()
    def reject_review(self, id, reviewer) -> None:
        """
        拒绝审核
        :param id: 文章id
        :param reviewer: 审核者
        :return:
        """
        command = "update review set `condition` = 'reject', reviewer = '%s' where id = '%s'" % (reviewer, id)
        self.execute(command)
        # self.db.commit()
    def query_unreview(self) -> str:
        """
        查询未审核文章
        :return: 文章
        """
        command = "select * from review natural join article where `condition` = 'unreview'"
        return self.execute(command)
    def query_review(self, coursename) -> str:
        """
        查询审核状态
        :return: 文章
        """
        command = "select * from review natural join article where `condition` = 'pass' and course = '%s'" % coursename
        return self.execute(command)

if __name__ == '__main__':
    db = Database()
    print(db.query_user('test'))
    